﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Common;


namespace AccessControl.DataAcess
{
	public class StudentCardHistoryAccess
	{
		public AccessControlDS.tblStudentCardHistoryDataTable SelectByStudent(String studentcode)
		{
			using (var cn = new SqlConnection(Util.ConnectionString))
			{
				var cmdSelect =
						new SqlCommand(String.Format(
								"SELECT ID,stcode,DateRegistered,[Description] FROM tblStudentCardHistory WHERE stcode=@stcode ORDER BY DateRegistered ASC"), cn);
				cmdSelect.Parameters.AddWithValue("@stcode", studentcode);
				var studentCardHisttoryTable = new AccessControlDS.tblStudentCardHistoryDataTable();
				var newRow = studentCardHisttoryTable.NewtblStudentCardHistoryRow();
				try
				{
					cn.Open();
					var reader = cmdSelect.ExecuteReader();
					if (reader != null)
						while (reader.Read())
						{
							newRow.ID= (String)reader["ID"];
							newRow.stcode = (String)reader["stcode"];
							newRow.DateRegistered = (DateTime)reader["DateRegistered"];
							newRow.Description= (String)reader["Description"];
						}
				}
				finally
				{
					cn.Close();
				}
					return studentCardHisttoryTable.Rows.Count != 0 ? studentCardHisttoryTable : null;
			}
		}

		public void Insert(AccessControlDS.tblStudentCardHistoryRow  row)
		{
			using (var cn = new SqlConnection(Util.ConnectionString))
			{
				var cmdInsert = new SqlCommand("spStudentCardHistory_Insert", cn) { CommandType = CommandType.StoredProcedure };
				cmdInsert.Parameters.AddWithValue("@stcode", row.stcode);
				cmdInsert.Parameters.AddWithValue("@DateRegistered", row.DateRegistered);
				cmdInsert.Parameters.AddWithValue("@Description", row.Description);
	
				try
				{
					cn.Open();
					cmdInsert.ExecuteNonQuery();
				}
				finally
				{
					cn.Close();
				}
			}
		}
	}
}
